package dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pojo.Book;
import util.DBUtil;
import dao.BookDao;

public class BookDaoImpl implements BookDao {

	/**
	 * add book to db one by one record
	 */
	public int addBook(Book book) {
		String sql="insert into books(id,name,pub_date,author,price,is_new,publisher) "
				+ "values (seq1.nextval,?,?,?,?,?,?)";
		Connection connection = null;
		PreparedStatement pst=null;
		int m=0;
		try {
			connection = DBUtil.createConnection();
			pst = connection.prepareStatement(sql);
			pst.setString(1, book.getName());
			pst.setDate(2, new java.sql.Date(book.getPublishDate().getTime()));
			pst.setString(3, book.getAuthor());
			pst.setDouble(4, book.getPrice());
			pst.setBoolean(5, book.getIsNewBook());
			pst.setString(6, book.getPublisher());
			m=pst.executeUpdate();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally{
			DBUtil.close(connection, pst, null);
		}
	}
	
	/**
	 * add a batch of books to db once
	 * @param books
	 * @return
	 */
	public int[] addBook2(Book[] books) {
		String sql="insert into books(id,name,pub_date,author,price,is_new,publisher) "
				+ "values (seq1.nextval,?,?,?,?,?,?)";
		Connection connection = null;
		PreparedStatement pst=null;
		int[] m;
		try {
			connection = DBUtil.createConnection();
			connection.setAutoCommit(false);
			
			pst = connection.prepareStatement(sql);
			for(int i=0; i<books.length; i++){
				pst.setString(1, books[i].getName());
				pst.setDate(2, new java.sql.Date(books[i].getPublishDate().getTime()));
				pst.setString(3, books[i].getAuthor());
				pst.setDouble(4, books[i].getPrice());
				pst.setBoolean(5, books[i].getIsNewBook());
				pst.setString(6, books[i].getPublisher());
				pst.addBatch();
			}
			m = pst.executeBatch();
			connection.commit();
			return m;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally{
			DBUtil.close(connection, pst, null);
		}
	}

	/**
	 * delete book by id
	 */
	public int deleteBook(Integer id) {
		String sql="delete from books where id=?";
		Connection connection = null;
		PreparedStatement pst=null;
		int m=0;
		try {
			connection = DBUtil.createConnection();
			pst = connection.prepareStatement(sql);
			pst.setInt(1, id);
			m=pst.executeUpdate();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally{
			DBUtil.close(connection, pst, null);
		}
	}

	/**
	 * update book
	 */
	public int updateBook(Book book) {
		String sql="update books set name=?,pub_date=?,author=?,price=?,is_new=?,publisher=? where id=?";
		Connection connection = null;
		PreparedStatement pst=null;
		int m=0;
		try {
			connection = DBUtil.createConnection();
			pst = connection.prepareStatement(sql);
			pst.setString(1, book.getName());
			pst.setDate(2, new java.sql.Date(book.getPublishDate().getTime()));
			pst.setString(3, book.getAuthor());
			pst.setDouble(4, book.getPrice());
			pst.setBoolean(5, book.getIsNewBook());
			pst.setString(6, book.getPublisher());
			pst.setInt(7, book.getId());
			m=pst.executeUpdate();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally{
			DBUtil.close(connection, pst, null);
		}
	}

	/**
	 * load one book by id
	 */
	public Book loadBook(Integer id) {
		String sql="select * from customers where id=?";
		Connection connection = null;
		PreparedStatement pst=null;
		Book book = null;
		try {
			connection = DBUtil.createConnection();
			pst = connection.prepareStatement(sql);
			pst.setInt(1, id);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				book = new Book();
				book.setName(rs.getString("name"));
				book.setPublishDate(rs.getDate("pub_date"));
				book.setAuthor(rs.getString("author"));
				book.setPrice(rs.getDouble("price"));
				book.setIsNewBook(rs.getBoolean("is_new"));
				book.setPublisher(rs.getString("publisher"));
			}
			return book;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally{
			DBUtil.close(connection, pst, null);
		}
	}

	/**
	 * load all books
	 */
	public List<Book> findAll() {
		List<Book> list = new ArrayList<Book>();
		
		String sql="select * from books";
		Connection connection = null;
		PreparedStatement pst=null;
		try {
			connection = DBUtil.createConnection();
			pst = connection.prepareStatement(sql);
			ResultSet rs = null;
			rs=pst.executeQuery();
			
			while(rs.next()){
				Book book = new Book();
				book.setId(rs.getInt("id"));
				book.setName(rs.getString("name"));
				book.setPublishDate(rs.getDate("pub_date"));
				book.setAuthor(rs.getString("author"));
				book.setPrice(rs.getDouble("price"));
				book.setIsNewBook(rs.getBoolean("is_new"));
				book.setPublisher(rs.getString("publisher"));
				list.add(book);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		} finally{
			DBUtil.close(connection, pst, null);
		}
	}
	
}
